import re
import copy
import json
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from typing import Dict, List


class TemplateReader:

    def __init__(self, file_name, sheet_name):
        self.workbook = load_workbook(filename=file_name)
        self.sheet = self.workbook[sheet_name]
        self.area_titles = ["检 测 报 告", "检测结果汇总表", "No.1 检测要求", "No.2 检测数据", "No.3 检测曲线", "No.4 检测照片", "No.5 检测设备", "No.6 变更履历"]
        self.column = "B"

    def save(self, file_name):
        self.workbook.save(file_name)

    def insert_rows(self, start_row: int, amount: int):
        """
        向报告中插入n行，不改变其他地方的样式

        :param worksheet: 工作表
        :param start_row: 起始行，从1开始数
        :param amount: 加入的行数
        """
        ws = self.sheet
        fmt = "{min_col}{min_row}:{max_col}{max_row}"
        unmerged_cells: List[str] = []
        merged_cells: List[str] = []
        row_heights: Dict[int, int] = {}

        # 记录每一行的高度
        for row in range(start_row, ws.max_row + 1):
            row_heights[row] = ws.row_dimensions[row].height

        # 处理合并单元格
        for range_cell in ws.merged_cells:
            min_row, max_row, min_col, max_col = (range_cell.min_row,
                                                  range_cell.max_row,
                                                  range_cell.min_col,
                                                  range_cell.max_col)
            # 处理原来的合并单元格
            if max_row >= start_row:
                unmerged_cells.append(str(range_cell))
                merged_cells.append(
                    fmt.format(min_col=get_column_letter(min_col),
                               min_row=min_row + amount,
                               max_col=get_column_letter(max_col),
                               max_row=max_row + amount))
        new_merged_cells = []
        for i in range(amount):
            for cell in ws[start_row + i]:
                source_cell = ws.cell(row=start_row, column=cell.column)
                merged_area = self.find_merged_range(source_cell.coordinate)
                # 如果原来的某个单元格属于被合并的单元格
                if merged_area:
                    # 如果是在同一行合并, 那么新行仅在该行进行合并
                    if merged_area.min_row == merged_area.max_row:
                        s = fmt.format(
                            min_col=get_column_letter(merged_area.min_col),
                            min_row=merged_area.min_row + i,
                            max_col=get_column_letter(merged_area.max_col),
                            max_row=merged_area.max_row + i)
                        new_merged_cells.append(s)
                    # 如果是在多行合并, 那么新行并入原来合并区域
                    else:
                        if merged_area.coord in merged_cells:
                            merged_cells.remove(merged_area.coord)
                        s = fmt.format(
                            min_col=get_column_letter(merged_area.min_col),
                            min_row=merged_area.min_row,
                            max_col=get_column_letter(merged_area.max_col),
                            max_row=merged_area.max_row + amount)
                        new_merged_cells.append(s)
        merged_cells = list(set(merged_cells + new_merged_cells))
        # 取消原有的合并单元格
        for range_cell in unmerged_cells:
            ws.unmerge_cells(range_cell)
        # 插入行
        ws.insert_rows(start_row, amount=amount)
        # 恢复行高
        for row, height in row_heights.items():
            ws.row_dimensions[row + amount].height = height
        for i in range(amount):
            ws.row_dimensions[start_row + i].height = ws.row_dimensions[start_row].height
        # 拷贝样式
        for i in range(amount):
            for cell in ws[start_row + i]:
                source_cell = ws.cell(row=start_row + amount, column=cell.column)
                self.cell_copy(source_cell, cell)
        # 重新合并单元格
        for range_cell in merged_cells:
            ws.merge_cells(range_cell)
        # 打印区域向下扩展
        self.set_printarea(amount=amount)

    @staticmethod
    def cell_copy(src_cell, dst_cell):
        dst_cell.font = copy.copy(src_cell.font)
        dst_cell.border = copy.copy(src_cell.border)
        dst_cell.number_format = copy.copy(src_cell.number_format)
        dst_cell.alignment = copy.copy(src_cell.alignment)
        dst_cell.value = copy.copy(src_cell.value)

    def find_merged_range(self, coord):
        merged_cells = self.sheet.merged_cells.ranges
        for merged_cell in merged_cells:
            if coord in merged_cell:
                return merged_cell
        return None

    def set_printarea(self, amount):
        """
        将打印区域向下平移amount行
        """
        if self.sheet.print_area:
            s = self.sheet.print_area
            pattern = r'\$(\d+)$'
            replacement = lambda m: '$' + str(int(m.group(1)) + amount)
            self.sheet.print_area = re.sub(pattern, replacement, s)
 
    def extract_template(self) -> dict:
        report_areas = self.split_templates()
        min_rects = self._find_min_rectangles()
        max_rects = self._find_max_rectangles(min_rects)
        regions = self._map_rectangles_to_areas(report_areas, max_rects)
        row_titles, column_titles, content_regions = self._separateContentRegions(min_rects)
        elements = self._find_headers(row_titles, column_titles, content_regions)
        template_conf = reader._map_elements_to_regions(regions, elements)
        return template_conf

    def _find_min_rectangles(self):
        ws = self.sheet

        # 确定表格的实际大小
        rows = ws.max_row
        cols = ws.max_column

        # 初始化单元格边框信息
        cells = []
        for i in range(1, rows + 1):
            row = []
            for j in range(1, cols + 1):
                cell = ws.cell(row=i, column=j)
                border = cell.border
                left = border.left.style is not None
                right = border.right.style is not None
                top = border.top.style is not None
                bottom = border.bottom.style is not None
                row.append({'left': left, 'right': right, 'top': top, 'bottom': bottom})
            cells.append(row)

        # 添加进去合并单元格
        for range_cell in ws.merged_cells:
            min_row, max_row, min_col, max_col = (range_cell.min_row, range_cell.max_row, range_cell.min_col,range_cell.max_col)
            left_top = ws.cell(row=min_row, column=min_col)
            border = left_top.border
            left = border.left.style is not None
            right = border.right.style is not None
            top = border.top.style is not None
            bottom = border.bottom.style is not None
            # 去除最左上角单元格的右边框还有下边框
            cells[min_row-1][min_col-1]['right'] = False
            cells[min_row-1][min_col-1]['bottom'] = False
            if left:
                # 合并区域的最左边一列加左边框
                for i in range(min_row, max_row+1):
                    cells[i-1][min_col-1]['left'] = True
            if right:
                # 合并区域的最右边一列加上右边框
                for i in range(min_row, max_row+1):
                    cells[i-1][max_col-1]['right'] = True
            if top:
                # 合并区域的最上边一行加上边框
                for j in range(min_col, max_col+1):
                    cells[min_row-1][j-1]['top'] = True
            if bottom:
                # 合并区域的最下边一行加上下边框
                for j in range(min_col, max_col+1):
                    cells[max_row-1][j-1]['bottom'] = True

        # 添加进去相邻关系：
        for i in range(rows):
            for j in range(cols):
                cell = cells[i][j]
                if cell['left'] and j > 0:
                    cells[i][j-1]['right'] = True
                if cell['right'] and j < cols-1:
                    cells[i][j+1]['left'] = True
                if cell['top'] and i > 0:
                    cells[i-1][j]['bottom'] = True
                if cell['bottom'] and i < rows-1:
                    cells[i+1][j]['top'] = True

        rows = len(cells)
        if rows == 0:
            return []
        cols = len(cells[0])
        # 预处理列的前缀和
        col_left = [[0] * (rows + 1) for _ in range(cols)]
        col_right = [[0] * (rows + 1) for _ in range(cols)]
        for j in range(cols):
            for i in range(1, rows + 1):
                cell = cells[i-1][j]
                col_left[j][i] = col_left[j][i-1] + (1 if cell['left'] else 0)
                col_right[j][i] = col_right[j][i-1] + (1 if cell['right'] else 0)

        # 预处理行的前缀和
        row_top = [[0] * (cols + 1) for _ in range(rows)]
        row_bottom = [[0] * (cols + 1) for _ in range(rows)]
        for i in range(rows):
            for j in range(1, cols + 1):
                cell = cells[i][j-1]
                row_top[i][j] = row_top[i][j-1] + (1 if cell['top'] else 0)
                row_bottom[i][j] = row_bottom[i][j-1] + (1 if cell['bottom'] else 0)

        # 预处理二维前缀和
        sum_bottom = [[0] * (cols + 1) for _ in range(rows + 1)]
        sum_right = [[0] * (cols + 1) for _ in range(rows + 1)]
        for i in range(rows):
            for j in range(cols):
                sum_bottom[i+1][j+1] = sum_bottom[i+1][j] + sum_bottom[i][j+1] - sum_bottom[i][j] + (1 if cells[i][j]['bottom'] else 0)
                sum_right[i+1][j+1] = sum_right[i+1][j] + sum_right[i][j+1] - sum_right[i][j] + (1 if cells[i][j]['right'] else 0)

        rectangles = []

        # 遍历所有可能的矩形
        for i1 in range(rows):
            for j1 in range(cols):
                # 限制右下角的范围，从当前左上角开始
                for i2 in range(i1, rows):
                    for j2 in range(j1, cols):
                        # 检查左边界
                        left_total = col_left[j1][i2+1] - col_left[j1][i1]
                        expected = i2 - i1 + 1
                        if left_total != expected:
                            continue
                        # 检查右边界
                        right_total = col_right[j2][i2+1] - col_right[j2][i1]
                        if right_total != expected:
                            continue
                        # 检查上边界
                        top_total = row_top[i1][j2+1] - row_top[i1][j1]
                        expected_col = j2 - j1 + 1
                        if top_total != expected_col:
                            continue
                        # 检查下边界
                        bottom_total = row_bottom[i2][j2+1] - row_bottom[i2][j1]
                        if bottom_total != expected_col:
                            continue
                        # 检查内部横向分割线
                        bottom_area = sum_bottom[i2][j2+1] - sum_bottom[i1][j2+1] - sum_bottom[i2][j1] + sum_bottom[i1][j1]
                        if bottom_area != 0:
                            continue
                        # 检查内部纵向分割线
                        right_area = sum_right[i2+1][j2] - sum_right[i1][j2] - sum_right[i2+1][j1] + sum_right[i1][j1]
                        if right_area != 0:
                            continue
                        # 转换为Excel的1-based索引
                        start_row = i1 + 1
                        start_col = j1 + 1
                        end_row = i2 + 1
                        end_col = j2 + 1
                        rectangles.append(((start_row, start_col), (end_row, end_col)))
        return rectangles

    @staticmethod
    def _find_max_rectangles(min_rectangles):
        # 将每个最小矩形转换为包含坐标和组成部分的结构
        rectangles = [{'coords': rect, 'components': [rect]} for rect in min_rectangles]

        def can_merge_horizontal(rect1, rect2):
            (s1_row, s1_col), (e1_row, e1_col) = rect1['coords']
            (s2_row, s2_col), (e2_row, e2_col) = rect2['coords']
            # 检查是否同一行范围且当前矩形的右列紧邻下一个矩形的左列
            return (s1_row == s2_row and e1_row == e2_row and e1_col + 1 == s2_col)

        def can_merge_vertical(rect1, rect2):
            (s1_row, s1_col), (e1_row, e1_col) = rect1['coords']
            (s2_row, s2_col), (e2_row, e2_col) = rect2['coords']
            # 检查是否同一列范围且当前矩形的下行紧邻下一个矩形的上行
            return (s1_col == s2_col and e1_col == e2_col and e1_row + 1 == s2_row)

        changed = True
        while changed:
            changed = False
            
            # 水平合并
            sorted_h = sorted(rectangles, key=lambda x: (x['coords'][0][0], x['coords'][0][1]))
            new_rects = []
            i = 0
            while i < len(sorted_h):
                current = sorted_h[i]
                j = i + 1
                while j < len(sorted_h):
                    next_rect = sorted_h[j]
                    if can_merge_horizontal(current, next_rect):
                        # 合并坐标
                        new_coords = (current['coords'][0], next_rect['coords'][1])
                        # 合并组成部分
                        new_components = current['components'] + next_rect['components']
                        current = {'coords': new_coords, 'components': new_components}
                        j += 1
                        changed = True
                    else:
                        break
                new_rects.append(current)
                i = j
            rectangles = new_rects
            
            # 垂直合并
            sorted_v = sorted(rectangles, key=lambda x: (x['coords'][0][1], x['coords'][0][0]))
            new_rects = []
            i = 0
            while i < len(sorted_v):
                current = sorted_v[i]
                j = i + 1
                while j < len(sorted_v):
                    next_rect = sorted_v[j]
                    if can_merge_vertical(current, next_rect):
                        # 合并坐标
                        new_coords = (current['coords'][0], next_rect['coords'][1])
                        # 合并组成部分
                        new_components = current['components'] + next_rect['components']
                        current = {'coords': new_coords, 'components': new_components}
                        j += 1
                        changed = True
                    else:
                        break
                new_rects.append(current)
                i = j
            rectangles = new_rects

        # 返回结果，包含坐标和组成部分
        return [rect['coords'] for rect in rectangles]


    def split_templates(self):
        """解析报告
        将测试报告分成不同区域，基于B列中的特定标题确定每个区域的行范围。
        """
        ws = self.sheet
        areas = {}
        title_rows = []
        current_search_row = 1

        for title in self.area_titles:
            for row in range(current_search_row, ws.max_row + 1):
                cell = ws[f'{self.column}{row}']
                if title in str(cell.value):
                    title_rows.append((title, row))
                    current_search_row = row + 1
                    break

        for i in range(len(title_rows)):
            title, start_row = title_rows[i]
            if i < len(title_rows) - 1:
                end_row = title_rows[i + 1][1] - 1
            else:
                end_row = ws.max_row
            areas[title] = [start_row, end_row]

        self.report_areas = areas
        return areas

    @staticmethod
    def _map_rectangles_to_areas(report_areas, rectangles):    
        """将矩形坐标映射到报告区域"""
        # 假设 self.report_areas 是之前通过 extract_template() 生成的区域划分结果
        # 结构示例：{"检测报告": [5,6], "检测结果汇总表": [7,8], ...}
        
        area_mapping = {title: [] for title in report_areas.keys()}
        
        for rect in rectangles:
            top_left_row = rect[0][0]  # 左上角的行号
            matched = False
            
            # 遍历所有区域，按标题顺序检查匹配
            for title in report_areas:
                start, end = report_areas[title]
                if start <= top_left_row <= end:
                    area_mapping[title].append(rect)
                    matched = True
                    break  # 匹配到第一个区域后立即停止
            
            # 可选：处理未匹配的矩形
            if not matched:
                pass
        
        return area_mapping

    def _separateContentRegions(self, min_rectangles):
        ws = self.sheet
        header_regions = []
        content_regions = []
        separate_lines = set()
        for min_rectangle in min_rectangles:
            (min_row, min_col), (max_row, max_col) = min_rectangle
            header_content = ""
            for row in ws.iter_rows(
                min_row=min_row,
                max_row=max_row,
                min_col=min_col,
                max_col=max_col,
                values_only=True 
            ):
                for cell_value in row:
                    if cell_value:
                        header_content = header_content + str(cell_value)
            if header_content == "" or "签发日期" in header_content:
                content_regions.append({"pos": min_rectangle, "value": ""})
                for row in range(min_row, max_row+1):
                    separate_lines.add(row)
            else:
                header_regions.append({"pos": min_rectangle, "value": header_content})
        row_titles = [item for item in header_regions if item['pos'][0][0] in separate_lines or item['pos'][1][0] in separate_lines]
        column_titles = [item for item in header_regions if item not in row_titles]
        return row_titles, column_titles, content_regions

    @staticmethod
    def _find_headers(row_headers, column_headers, content_regions):
        # Prepare all regions with type
        all_regions = []
        for rh in row_headers:
            rh['type'] = 'row_header'
            all_regions.append(rh)
        for ch in column_headers:
            ch['type'] = 'column_header'
            all_regions.append(ch)
        for ct in content_regions:
            ct['type'] = 'content'
            all_regions.append(ct)
        
        # Assign left_ptr and top_ptr for each region
        for a in all_regions:
            a_min_row, a_min_col = a['pos'][0]
            a_max_row, a_max_col = a['pos'][1]
            
            # Find left_ptr
            left_candidates = []
            for b in all_regions:
                if b is a:
                    continue
                b_min_row, b_min_col = b['pos'][0]
                b_max_row, b_max_col = b['pos'][1]
                if b_max_col < a_min_col:
                    if b_min_row <= a_min_row and b_max_row >= a_max_row:
                        if b_max_row - b_min_row > a_max_row - a_min_row:
                            left_candidates.append(b)
            # Sort candidates by max_col descending
            left_candidates.sort(key=lambda x: x['pos'][1][1], reverse=True)
            a['left_ptr'] = left_candidates[0] if left_candidates else None
            
            # Find top_ptr
            top_candidates = []
            for b in all_regions:
                if b is a:
                    continue
                b_min_row, b_min_col = b['pos'][0]
                b_max_row, b_max_col = b['pos'][1]
                if b_max_row < a_min_row:
                    if b_min_col <= a_min_col and b_max_col >= a_max_col:
                        if b_max_col - b_min_col > a_max_col - a_min_col:
                            top_candidates.append(b)
            # Sort candidates by max_row descending
            top_candidates.sort(key=lambda x: x['pos'][1][0], reverse=True)
            a['top_ptr'] = top_candidates[0] if top_candidates else None
        
        # Process each content region to collect left and top headers
        result = []
        for content in content_regions:
            # Collect left headers (row_headers)
            left_list = []
            current = content.get('left_ptr')
            while current is not None:
                if current['type'] == 'row_header':
                    left_list.append(current)
                    current = current.get('left_ptr')
                else:
                    break
            
            # Collect top headers (column_headers)
            top_list = []
            current = content.get('top_ptr')
            while current is not None:
                if current['type'] == 'column_header':
                    top_list.append(current)
                    current = current.get('top_ptr')
                else:
                    break
            left_list.sort(key=lambda x: x['pos'][0][0])
            top_list.sort(key=lambda x: x['pos'][0][0])
            formatted_left =[item['value'] for item in left_list]
            formatted_top = [item['value'] for item in top_list]
            result.append({
                'pos': content['pos'][0],
                'left': formatted_left,
                'top': formatted_top
            })
        
        return result

    @staticmethod
    def _map_elements_to_regions(regions_dict, elements_list):
        def is_point_in_rect(point, rect):
            (x1, y1), (x2, y2) = rect
            px, py = point
            return x1 <= px <= x2 and y1 <= py <= y2
        result = {}
        for region_name, rectangles in regions_dict.items():
            region_entries = []
            
            for rect in rectangles:
                left_top = rect[0]
                right_bottom = rect[1]
                
                placeholders = []
                for element in elements_list:
                    if is_point_in_rect(element['pos'], (left_top, right_bottom)):
                        placeholders.append(element)
                
                region_entries.append({
                    "table_range": rect,
                    "table_contents": placeholders
                })
            result[region_name] = region_entries

        return result

if __name__ == "__main__":
    file_name = r"d:\报告模板\Pack热扩散检测报告模板V1.1_20250411.xlsx"
    sheet_name = "3 Pack 热扩散检测报告模板"
    dst_filename = "热扩散01.xlsx"
    reader = TemplateReader(file_name, sheet_name)
    template_conf = reader.extract_template()
    with open("haha.json", 'w', encoding='utf-8') as file:
        json.dump(template_conf, file, ensure_ascii=False)

    # print(area_mapping)
